<?php
namespace app\controller;

use app\BaseController;
use think\facade\Db;
use think\Request;
use think\facade\View;
use think\helper\Str;
use think\exception\ValidateException;


// E-learning统计数据
class LearningContent extends BaseController
{
    public function index()
    {
        return View::fetch();
    }


    //+数据加载
    public function load(Request $request)
    {
        if(!check_jurisdiction(session('admin_auth.role_id'), $this->control, $this->action)) {
            return json(['code' => 500, 'data' => '', 'msg' => '无操作权限']);
        }

        $get = $request->get();

        if (isset($get['field']) && isset($get['order'])) {
            if (!empty($get['field']) && !empty($get['order'])) {
                $field = $get['field'];
                $sort = $get['order'];
            } else {
                $field = 'id';
                $sort = 'asc';
            }
        } else {
            $field = 'id';
            $sort = 'asc';
        }

        $where = [];

        if (isset($get['keys'])) {
            if (!empty($get['keys'])) {
                $where[] = ['a.title|a.id', 'like', '%' . $get['keys'] . '%'];
            }
        }

        $where[] = ['a.status', '=', 1];


        // 取得满足条件的记录数

        //+学习人数
        $subQuery1 = Db::name('learning_user')
            ->field('COUNT(userid) as total, learning_id')
            ->group('learning_id')
            ->buildSql();

        //+学习时长
        $subQuery2 = Db::name('learning_log')
            ->field('SUM(learning_time) as duration, learning_id')
            ->group('learning_id')
            ->buildSql();

        //+播放量
        $subQuery3 = Db::name('learning_play_record')
            ->field('COUNT(id) as play_total, learning_id')
            ->group('learning_id')
            ->buildSql();

        //+分享量
        $subQuery4 = Db::name('learning_share_record')
            ->field('COUNT(id) as share_total, learning_id')
            ->group('learning_id')
            ->buildSql();

        //+话题
        $subQuery5 = Db::name('learning_topic')
            ->field('COUNT(id) as topic_total, learning_id')
            ->group('learning_id')
            ->buildSql();

        //+评论
        $subQuery6 = Db::table('o_learning_topic')
            ->alias('lt')
            ->join('o_comment ct', 'ct.aid=lt.id', 'left')
            ->where('ct.type', '=', 3)
            ->field('COUNT(ct.id) as comment_total, lt.learning_id')
            ->group('learning_id')
            ->buildSql();

        $subQuery7 = Db::table('o_learning_step_resource')
            ->alias('a')
            ->join('o_courseware b', 'b.id=a.courseware_id')
            ->where('b.type', '<>', 4)
            ->where('b.type', '<>', 5)
            ->where('a.status', '=', 1)
            ->field('COUNT(a.id) as resource_total, a.learning_id')
            ->group('a.learning_id')
            ->buildSql();

        $total = Db::table('o_learning')
            ->alias('a')
            ->join($subQuery1.'b','a.id=b.learning_id','left')
            ->join($subQuery2.'c','a.id=c.learning_id','left')
            ->join($subQuery3.'d','a.id=d.learning_id','left')
            ->join($subQuery4.'e','a.id=e.learning_id','left')
            ->join($subQuery5.'f','a.id=f.learning_id','left')
            ->join($subQuery6.'g','a.id=g.learning_id','left')
            ->join($subQuery7.'h','a.id=h.learning_id','left')
            ->where($where)
            ->count();


        // 分页查询数据
        $rows = Db::table('o_learning')
            ->alias('a')
            ->join($subQuery1.'b','a.id=b.learning_id','left')
            ->join($subQuery2.'c','a.id=c.learning_id','left')
            ->join($subQuery3.'d','a.id=d.learning_id','left')
            ->join($subQuery4.'e','a.id=e.learning_id','left')
            ->join($subQuery5.'f','a.id=f.learning_id','left')
            ->join($subQuery6.'g','a.id=g.learning_id','left')
            ->join($subQuery7.'h','a.id=h.learning_id','left')
            ->where($where)
            ->order("$field", "$sort")
            ->page($get['page'], $get['limit'])
            ->field('a.id, a.title, a.update_time, b.total, c.duration, d.play_total, e.share_total, f.topic_total, g.comment_total, h.resource_total')
            ->select()->toArray();

        foreach ($rows as &$v) {
            if (empty($v['total'])) {
                $v['total'] = 0;
            }


            if (empty($v['duration'])) {
                $v['duration'] = 0;
            } else {
                $v['duration'] = get_se_to_time($v['duration']);
            }

            if (empty($v['play_total'])) {
                $v['play_total'] = 0;
            }
            if (empty($v['share_total'])) {
                $v['share_total'] = 0;
            }
            if (empty($v['topic_total'])) {
                $v['topic_total'] = 0;
            }
            if (empty($v['comment_total'])) {
                $v['comment_total'] = 0;
            }


            $survey = Db::name('learning_step_resource')
                ->alias('a')
                ->join('o_courseware b', 'b.id=a.courseware_id')
                ->join('o_courseware_survey c', 'c.courseware_id=b.id')
                ->where('a.learning_id', '=', $v['id'])
                ->where('b.type', '=', 4)
                ->where('a.status', '=', 1)
                ->field('a.id, a.courseware_id, c.relation')
                ->select()
                ->toArray();

            $guanlian = [];
            $buguanlian = [];
            foreach ($survey as &$value) {
                if ($value['relation'] == 1) {
                    array_push($guanlian, $value['id']);
                } else {
                    array_push($buguanlian, $value['id']);
                }
            }
            $userid = Db::name('learning_log')
                ->where('learning_id', '=', $v['id'])
                ->where('is_finish', '=', 1)
                ->where('resource_id', 'not in', $buguanlian)
                ->field('COUNT(id) as log, userid, learning_id')
                ->group('userid')
                ->select()
                ->toArray();
            foreach ($userid as $key => $val) {
                if (($v['resource_total'] + count($guanlian)) != $val['log']) {
                    unset($userid[$key]);
                }
            }

            $v['complete_total'] = count($userid);
        }

        $data = array(
            'code'=> 0,
            'msg'=> '',
            'count' => $total,
            'data'=> $rows
        );
        return json($data);
    }


    public function export(Request $request)
    {
        if(!check_jurisdiction(session('admin_auth.role_id'), $this->control, $this->action)) {
            return $this->outputAjax('无操作权限');
        }

        $get = $request->get();

        $where = [];

        if (isset($get['keys'])) {
            if (!empty($get['keys'])) {
                $where[] = ['a.title|a.id', 'like', '%' . $get['keys'] . '%'];
            }
        }

        $where[] = ['a.status', '=', 1];

        set_time_limit(0);
        //设置程序运行内存
        ini_set('memory_limit', '4096M');

        $fileName = 'E-learning学习数据'.date('Ymd', time());
        header('Content-Encoding: UTF-8');
        header("Content-type:application/vnd.ms-excel;charset=UTF-8");
        header('Content-Disposition: attachment;filename="' . $fileName . '.csv"');


        //打开php标准输出流
        $fp = fopen('php://output', 'a');

        //添加BOM头，以UTF8编码导出CSV文件，如果文件头未添加BOM头，打开会出现乱码。
        fwrite($fp, chr(0xEF).chr(0xBB).chr(0xBF));


        //添加导出标题
        fputcsv($fp, ['ID', '课程名称', '学习人数', '完成人数', '学习时长(H)', '播放量', '分享量', '发帖', '评论', '更新时间']);

        //链接数据库

        //+学习人数
        $subQuery1 = Db::name('learning_user')
            ->field('COUNT(userid) as total, learning_id')
            ->group('learning_id')
            ->buildSql();

        //+学习时长
        $subQuery2 = Db::name('learning_log')
            ->field('SUM(learning_time) as duration, learning_id')
            ->group('learning_id')
            ->buildSql();

        //+播放量
        $subQuery3 = Db::name('learning_play_record')
            ->field('COUNT(id) as play_total, learning_id')
            ->group('learning_id')
            ->buildSql();

        //+分享量
        $subQuery4 = Db::name('learning_share_record')
            ->field('COUNT(id) as share_total, learning_id')
            ->group('learning_id')
            ->buildSql();

        //+话题
        $subQuery5 = Db::name('learning_topic')
            ->field('COUNT(id) as topic_total, learning_id')
            ->group('learning_id')
            ->buildSql();

        //+评论
        $subQuery6 = Db::table('o_learning_topic')
            ->alias('lt')
            ->join('o_comment ct', 'ct.aid=lt.id', 'left')
            ->where('ct.type', '=', 3)
            ->field('COUNT(ct.id) as comment_total, lt.learning_id')
            ->group('learning_id')
            ->buildSql();

        $subQuery7 = Db::table('o_learning_step_resource')
            ->alias('a')
            ->join('o_courseware b', 'b.id=a.courseware_id')
            ->where('b.type', '<>', 4)
            ->where('b.type', '<>', 5)
            ->where('a.status', '=', 1)
            ->field('COUNT(a.id) as resource_total, a.learning_id')
            ->group('a.learning_id')
            ->buildSql();

        $total = Db::table('o_learning')
            ->alias('a')
            ->join($subQuery1.'b','a.id=b.learning_id','left')
            ->join($subQuery2.'c','a.id=c.learning_id','left')
            ->join($subQuery3.'d','a.id=d.learning_id','left')
            ->join($subQuery4.'e','a.id=e.learning_id','left')
            ->join($subQuery5.'f','a.id=f.learning_id','left')
            ->join($subQuery6.'g','a.id=g.learning_id','left')
            ->join($subQuery7.'h','a.id=h.learning_id','left')
            ->where($where)
            ->count();

        $nums = 100; //每次导出数量
        $step = ceil($total/$nums); //循环次数

        for($i = 0; $i < $step; $i++) {
            $start = $i * $nums;
            // 分页查询数据
            $rows = Db::table('o_learning')
                ->alias('a')
                ->join($subQuery1.'b','a.id=b.learning_id','left')
                ->join($subQuery2.'c','a.id=c.learning_id','left')
                ->join($subQuery3.'d','a.id=d.learning_id','left')
                ->join($subQuery4.'e','a.id=e.learning_id','left')
                ->join($subQuery5.'f','a.id=f.learning_id','left')
                ->join($subQuery6.'g','a.id=g.learning_id','left')
                ->join($subQuery7.'h','a.id=h.learning_id','left')
                ->where($where)
                ->limit($start, $nums)
                ->field('a.id, a.title, a.update_time, b.total, c.duration, d.play_total, e.share_total, f.topic_total, g.comment_total, h.resource_total')
                ->select()
                ->toArray();

            foreach ($rows as &$v) {
                if (empty($v['total'])) {
                    $v['total'] = 0;
                }


                if (empty($v['duration'])) {
                    $duration = 0;
                } else {
                    $duration = get_se_to_time($v['duration']);
                }

                if (empty($v['play_total'])) {
                    $v['play_total'] = 0;
                }
                if (empty($v['share_total'])) {
                    $v['share_total'] = 0;
                }
                if (empty($v['topic_total'])) {
                    $v['topic_total'] = 0;
                }
                if (empty($v['comment_total'])) {
                    $v['comment_total'] = 0;
                }


                $survey = Db::name('learning_step_resource')
                    ->alias('a')
                    ->join('o_courseware b', 'b.id=a.courseware_id')
                    ->join('o_courseware_survey c', 'c.courseware_id=b.id')
                    ->where('a.learning_id', '=', $v['id'])
                    ->where('b.type', '=', 4)
                    ->where('a.status', '=', 1)
                    ->field('a.id, a.courseware_id, c.relation')
                    ->select()
                    ->toArray();

                $guanlian = [];
                $buguanlian = [];
                foreach ($survey as &$value) {
                    if ($value['relation'] == 1) {
                        array_push($guanlian, $value['id']);
                    } else {
                        array_push($buguanlian, $value['id']);
                    }
                }
                $userid = Db::name('learning_log')
                    ->where('learning_id', '=', $v['id'])
                    ->where('is_finish', '=', 1)
                    ->where('resource_id', 'not in', $buguanlian)
                    ->field('COUNT(id) as log, userid, learning_id')
                    ->group('userid')
                    ->select();
                foreach ($userid as $key => $val) {
                    if (($v['resource_total'] + count($guanlian)) != $val['log']) {
                        unset($userid[$key]);
                    }
                }

                $complete_total = count($userid);

                $arr = [
                    $v['id'],
                    $v['title'],
                    $v['total'],
                    $complete_total,
                    $duration,
                    $v['play_total'],
                    $v['share_total'],
                    $v['topic_total'],
                    $v['comment_total'],
                    date('Y-m-d H:i:s',$v['update_time'])
                ];
                fputcsv($fp, $arr);
            }

            //每1千条数据就刷新缓冲区
            ob_flush();
            flush();
        }
    }


    public function user(Request $request)
    {
        $learning_id = $request->get('learning_id');
        View::assign('learning_id', $learning_id);
        return View::fetch();
    }


    public function getCount(Request $request)
    {
        $learning_id = $request->get('learning_id');

        //+学习人数
        $total = Db::table('o_learning_user')
            ->alias('a')
            ->join('o_members c','a.userid=c.userid','left')
            ->where('a.learning_id', '=', $learning_id)
            ->group('a.userid')
            ->count();

        //+学习时长
        $duration = Db::name('learning_log')
            ->where('learning_id', '=', $learning_id)
            ->sum('learning_time');


        //+播放量
        $play_total = Db::name('learning_play_record')
            ->where('learning_id', '=', $learning_id)
            ->count();

        //+分享量
        $share_total = Db::name('learning_share_record')
            ->where('learning_id', '=', $learning_id)
            ->count();

        //+话题
        $topic_total = Db::name('learning_topic')
            ->where('learning_id', '=', $learning_id)
            ->count();

        //+评论
        $topic_id = Db::name('learning_topic')
            ->where('learning_id', '=', $learning_id)
            ->column('id');

        $comment_total = Db::name('comment')
            ->where('type', '=', 3)
            ->where('aid', 'in', $topic_id)
            ->count();


        //+点赞
        $zan_total = Db::name('zan')
            ->where('type', '=', 3)
            ->where('aid', 'in', $topic_id)
            ->count();


        //+已学完
        $resource = Db::name('learning_step_resource')
            ->alias('a')
            ->join('o_courseware b', 'b.id=a.courseware_id', 'left')
            ->where('a.learning_id', '=', $learning_id)
            ->where('b.type', '<>', 4)
            ->where('b.type', '<>', 5)
            ->where('a.status', '=', 1)
            ->count();
        $survey = Db::name('learning_step_resource')
            ->alias('a')
            ->join('o_courseware b', 'b.id=a.courseware_id', 'left')
            ->join('o_courseware_survey c', 'c.courseware_id=b.id')
            ->where('a.learning_id', '=', $learning_id)
            ->where('b.type', '=', 4)
            ->where('a.status', '=', 1)
            ->field('a.id, a.courseware_id, c.relation')
            ->select()
            ->toArray();

        $guanlian = [];
        $buguanlian = [];
        foreach ($survey as &$value) {
            if ($value['relation'] == 1) {
                array_push($guanlian, $value['id']);
            } else {
                array_push($buguanlian, $value['id']);
            }
        }

        $userid = Db::table('o_learning_user')
            ->alias('a')
            ->join('o_members c','a.userid=c.userid','left')
            ->where('a.learning_id', '=', $learning_id)
            ->field('a.userid, a.learning_id')
            ->group('a.userid')
            ->select()
            ->toArray();
        foreach ($userid as $key => $val) {
            $learning_log_count = Db::name('learning_log')
                ->where('learning_id', '=', $learning_id)
                ->where('userid', '=', $val['userid'])
                ->where('is_finish', '=', 1)
                ->where('resource_id', 'not in', $buguanlian)
                ->group('resource_id')
                ->count();
            if (($resource + count($guanlian)) != $learning_log_count) {
                unset($userid[$key]);
            }
        }

        $finished = count($userid);

        $data = [
            'total' => $total,
            'complete_total' => $finished,
            'play_total' => $play_total,
            'share_total' => $share_total,
            'zan_total' => $zan_total,
            'topic_total' => $topic_total,
            'comment_total' => $comment_total,
            'duration' => floor((($duration)/3600)),
        ];

        return $this->outputAjax('获取成功', 0, $data);
    }


    public function getUserList(Request $request)
    {

        if(!check_jurisdiction(session('admin_auth.role_id'), $this->control, $this->action)) {
            return json(['code' => 500, 'data' => '', 'msg' => '无操作权限']);
        }

        $get = $request->get();

        $where = [];
        if (isset($get['tree_select_nodeId'])) {
            if (!empty($get['tree_select_nodeId'])) {
                $department = $get['tree_select_nodeId'];
                $arr = get_children($department, 1);
                $arr[] = $department;
                $department = implode('|', $arr);
                $where[]  = ['', 'EXP', Db::raw("CONCAT(',',c.department,',') REGEXP concat(',(',replace('".$department."',',','|'),'),')")];
            }
        }
        if (isset($get['key'])) {
            if (!empty($get['key'])) {
                $where[] = ['c.name|c.userid', 'like', '%' . $get['key'] . '%'];
            }
        }

        $where[] = ['a.learning_id', '=', $get['learning_id']];

        $where1 = [];
        if (isset($get['time'])) {
            if (!empty($get['time'])) {
                $t = explode(' - ', $get['time']);
                $where1[] = ['a.add_time', 'between', [strtotime($t[0]), strtotime($t[1])+86399]];
            }
        }


        //+学习时长
        $subQuery1 = Db::table('o_learning_log')
            ->alias('a')
            ->join('o_members c','a.userid=c.userid','left')
            ->where('a.learning_id', '=', $get['learning_id'])
            ->where($where1)
            ->field('SUM(a.learning_time) as duration, a.userid')
            ->group('a.userid')
            ->buildSql();

        $resource = Db::name('learning_step_resource')
            ->alias('a')
            ->join('o_courseware b', 'b.id=a.courseware_id')
            ->where('a.learning_id', '=', $get['learning_id'])
            ->where('b.type', '<>', 4)
            ->where('b.type', '<>', 5)
            ->where('a.status', '=', 1)
            ->count();

        $survey = Db::name('learning_step_resource')
            ->alias('a')
            ->join('o_courseware b', 'b.id=a.courseware_id')
            ->join('o_courseware_survey c', 'c.courseware_id=b.id')
            ->where('a.learning_id', '=', $get['learning_id'])
            ->where('b.type', '=', 4)
            ->where('a.status', '=', 1)
            ->field('a.id, a.courseware_id, c.relation')
            ->select()
            ->toArray();
        $guanlian = [];
        $buguanlian = [];
        foreach ($survey as &$val) {
            if ($val['relation'] == 1) {
                array_push($guanlian, $val['id']);
            } else {
                array_push($buguanlian, $val['id']);
            }
        }

        $rows = Db::table('o_learning_user')
            ->alias('a')
            ->join($subQuery1.'b','a.userid=b.userid','left')
            ->join('o_members c','a.userid=c.userid','left')
            ->where($where)
            ->field('a.id, a.learning_id, c.userid, c.name, c.department_name, c.division, b.duration')
            ->group('a.userid')
            ->select()
            ->toArray();
        if (!empty($rows)) {

            foreach ($rows as $k=>$v) {
                if (empty($v['duration'])) {
                    $rows[$k]['duration'] = 0;
                } else {
                    $rows[$k]['duration'] = get_se_to_time($v['duration']);
                }

                $start_time = Db::name('learning_log')
                    ->where('learning_id', '=', $get['learning_id'])
                    ->where('userid', '=', $v['userid'])
                    ->order('add_time', 'asc')
                    ->value('add_time');

                if (empty($start_time)) {
                    $rows[$k]['start_time'] = '--------';
                } else {
                    $rows[$k]['start_time'] = date('Y-m-d H:i', $start_time);
                }

                if (empty($v['duration'])) {
                    $rows[$k]['study_status'] = 3;
                    $rows[$k]['status_name'] = '未开始';
                } else {

                    $log = Db::name('learning_log')
                        ->where('userid', '=', $v['userid'])
                        ->where('learning_id', '=', $v['learning_id'])
                        ->where('is_finish', '=', 1)
                        ->where('resource_id', 'not in', $buguanlian)
                        ->group('resource_id')
                        ->count();
                    if (($resource + count($guanlian)) == $log) {
                        $rows[$k]['study_status'] = 1;
                        $rows[$k]['status_name'] = '已完成';
                    } else {
                        $rows[$k]['study_status'] = 2;
                        $rows[$k]['status_name'] = '学习中';
                    }
                }

                $count = mb_strlen($v['userid']);
                if ($count == 6) {
                    if (is_numeric($v['userid'])) {
                        $rows[$k]['carolid'] = '00'.$v['userid'];
                    } else {
                        $rows[$k]['carolid'] = $v['userid'];
                    }

                } else {
                    $rows[$k]['carolid'] = $v['userid'];
                }

                if (isset($get['time'])) {
                    if (!empty($get['time'])) {
                        $t = explode(' - ', $get['time']);
                        if (strtotime($t[0]) <= $start_time && strtotime($t[1])+86399 >= $start_time) {
                        } else {
                            unset($rows[$k]);
                        }
                    }
                }

                if (isset($get['study_status'])) {
                    if (!empty($get['study_status'])) {
                        if ($get['study_status'] == 1) {
                            if ($rows[$k]['study_status'] != 1) {
                                unset($rows[$k]);
                            }
                        } elseif ($get['study_status'] == 2) {
                            if ($rows[$k]['study_status'] != 2) {
                                unset($rows[$k]);
                            }
                        } elseif ($get['study_status'] == 3) {
                            if ($rows[$k]['study_status'] != 3) {
                                unset($rows[$k]);
                            }
                        }
                    }
                }

            }

            //+排序
            if (empty($rows)) {
                $user_data = [];
            } else {
                $user_data = sort_array($rows, 'duration', 'desc');
            }

            //+分页
            $limit = 5;
            $pages = $get['page'];
            if (empty($pages) || $pages == 1) {
                $Pages = 0;
            } else {
                $Pages = ($pages-1)*$limit;
            }

            $datas = array_slice($user_data, $Pages, $limit);

            $data = array(
                'code'=> 0,
                'msg'=> '',
                'count' => count($user_data),
                'data'=> $datas
            );
        } else {
            $data = array(
                'code'=> 0,
                'msg'=> '',
                'count' => 0,
                'data'=> []
            );
        }
        return json($data);
    }


    public function exportUserList(Request $request)
    {
        if(!check_jurisdiction(session('admin_auth.role_id'), $this->control, $this->action)) {
            return $this->outputAjax('无操作权限');
        }

        $get = $request->get();

        $where = [];
        if (isset($get['tree_select_nodeId'])) {
            if (!empty($get['tree_select_nodeId'])) {
                $department = $get['tree_select_nodeId'];
                $arr = get_children($department, 1);
                $arr[] = $department;
                $department = implode('|', $arr);
                $where[]  = ['', 'EXP', Db::raw("CONCAT(',',c.department,',') REGEXP concat(',(',replace('".$department."',',','|'),'),')")];
            }
        }
        if (isset($get['key'])) {
            if (!empty($get['key'])) {
                $where[] = ['c.name|c.userid', 'like', '%' . $get['key'] . '%'];
            }
        }

        $where[] = ['a.learning_id', '=', $get['learning_id']];

        $where1 = [];
        if (isset($get['time'])) {
            if (!empty($get['time'])) {
                $t = explode(' - ', $get['time']);
                $where1[] = ['a.add_time', 'between', [strtotime($t[0]), strtotime($t[1])+86399]];
            }
        }

        //+学习时长
        $subQuery1 = Db::table('o_learning_log')
            ->alias('a')
            ->join('o_members c','a.userid=c.userid','left')
            ->where('a.learning_id', '=', $get['learning_id'])
            ->where($where1)
            ->field('SUM(a.learning_time) as duration, a.userid')
            ->group('a.userid')
            ->buildSql();

        $resource = Db::name('learning_step_resource')
            ->alias('a')
            ->join('o_courseware b', 'b.id=a.courseware_id')
            ->where('a.learning_id', '=', $get['learning_id'])
            ->where('b.type', '<>', 4)
            ->where('b.type', '<>', 5)
            ->where('a.status', '=', 1)
            ->count();

        $survey = Db::name('learning_step_resource')
            ->alias('a')
            ->join('o_courseware b', 'b.id=a.courseware_id')
            ->join('o_courseware_survey c', 'c.courseware_id=b.id')
            ->where('a.learning_id', '=', $get['learning_id'])
            ->where('b.type', '=', 4)
            ->where('a.status', '=', 1)
            ->field('a.id, a.courseware_id, c.relation')
            ->select()
            ->toArray();
        $guanlian = [];
        $buguanlian = [];
        foreach ($survey as &$val) {
            if ($val['relation'] == 1) {
                array_push($guanlian, $val['id']);
            } else {
                array_push($buguanlian, $val['id']);
            }
        }

        $rows = Db::table('o_learning_user')
            ->alias('a')
            ->join($subQuery1.'b','a.userid=b.userid','left')
            ->join('o_members c','a.userid=c.userid','left')
            ->where($where)
            ->field('a.id, a.learning_id, c.userid, c.name, c.department_name, c.division, b.duration')
            ->group('a.userid')
            ->select()
            ->toArray();

        if (!empty($rows)) {

            foreach ($rows as $k => $v) {
                if (empty($v['duration'])) {
                    $rows[$k]['duration'] = 0;
                } else {
                    $rows[$k]['duration'] = get_se_to_time($v['duration']);
                }

                $start_time = Db::name('learning_log')
                    ->where('learning_id', '=', $get['learning_id'])
                    ->where('userid', '=', $v['userid'])
                    ->order('add_time', 'asc')
                    ->value('add_time');

                if (empty($start_time)) {
                    $rows[$k]['start_time'] = '--------';
                } else {
                    $rows[$k]['start_time'] = date('Y-m-d H:i', $start_time);
                }

                if (empty($v['duration'])) {
                    $rows[$k]['study_status'] = 3;
                    $rows[$k]['status_name'] = '未开始';
                } else {

                    $log = Db::name('learning_log')
                        ->where('userid', '=', $v['userid'])
                        ->where('learning_id', '=', $v['learning_id'])
                        ->where('is_finish', '=', 1)
                        ->where('resource_id', 'not in', $buguanlian)
                        ->group('resource_id')
                        ->count();
                    if (($resource + count($guanlian)) == $log) {
                        $rows[$k]['study_status'] = 1;
                        $rows[$k]['status_name'] = '已完成';
                    } else {
                        $rows[$k]['study_status'] = 2;
                        $rows[$k]['status_name'] = '学习中';
                    }
                }

                $count = mb_strlen($v['userid']);
                if ($count == 6) {
                    if (is_numeric($v['userid'])) {
                        $rows[$k]['carolid'] = "'00" . $v['userid'];
                    } else {
                        $rows[$k]['carolid'] = $v['userid'];
                    }

                } else {
                    $rows[$k]['carolid'] = $v['userid'];
                }

                if (isset($get['time'])) {
                    if (!empty($get['time'])) {
                        $t = explode(' - ', $get['time']);
                        if (strtotime($t[0]) <= $start_time && strtotime($t[1])+86399 >= $start_time) {
                        } else {
                            unset($rows[$k]);
                        }
                    }
                }

                if (isset($get['study_status'])) {
                    if (!empty($get['study_status'])) {
                        if ($get['study_status'] == 1) {
                            if ($rows[$k]['study_status'] != 1) {
                                unset($rows[$k]);
                            }
                        } elseif ($get['study_status'] == 2) {
                            if ($rows[$k]['study_status'] != 2) {
                                unset($rows[$k]);
                            }
                        } elseif ($get['study_status'] == 3) {
                            if ($rows[$k]['study_status'] != 3) {
                                unset($rows[$k]);
                            }
                        }
                    }
                }

            }

            //+排序
            if (empty($rows)) {
                $user_data = [];
            } else {
                $user_data = sort_array($rows, 'duration', 'desc');
            }

            set_time_limit(0);
            //设置程序运行内存
            ini_set('memory_limit', '4096M');

            $fileName = '员工个人Learnnow学习数据'.date('Ymd', time());
            header('Content-Encoding: UTF-8');
            header("Content-type:application/vnd.ms-excel;charset=UTF-8");
            header('Content-Disposition: attachment;filename="' . $fileName . '.csv"');


            //打开php标准输出流
            $fp = fopen('php://output', 'a');

            //添加BOM头，以UTF8编码导出CSV文件，如果文件头未添加BOM头，打开会出现乱码。
            fwrite($fp, chr(0xEF).chr(0xBB).chr(0xBF));


            //添加导出标题
            fputcsv($fp, ['工号', '姓名', 'Division', '部门', '学习时长(S)', '学习状态', '开始学习时间']);

            $total = count($user_data);

            $nums = 100; //每次导出数量
            $step = ceil($total/$nums); //循环次数

            for($i = 1; $i <= $step; $i++) {
                //+分页
                $limit = $nums;
                $pages = $i;
                if (empty($pages) || $pages == 1) {
                    $Pages = 0;
                } else {
                    $Pages = ($pages-1)*$limit;
                }

                $datas = array_slice($user_data, $Pages, $limit);

                foreach ($datas as &$v) {
                    $arr = [
                        $v['carolid'],
                        $v['name'],
                        $v['division'],
                        $v['department_name'],
                        $v['duration'],
                        $v['status_name'],
                        $v['start_time'],
                    ];
                    fputcsv($fp, $arr);
                }
                //每1千条数据就刷新缓冲区
                ob_flush();
                flush();
            }
        }
    }

    public function chapter(Request $request)
    {
        $learning_id = $request->get('learning_id');
        View::assign('learning_id', $learning_id);
        return View::fetch();
    }



    public function getChapterrList(Request $request)
    {
        if(!check_jurisdiction(session('admin_auth.role_id'), $this->control, $this->action)) {
            return json(['code' => 500, 'data' => '', 'msg' => '无操作权限']);
        }

        $get = $request->get();

        $where = [];

        if (isset($get['type'])) {
            if (!empty($get['type'])) {
                $where[] = ['f.type', '=', $get['type']];
            }
        }

        if (isset($get['title'])) {
            if (!empty($get['title'])) {
                $where[] = ['f.title', 'like', '%'.$get['title'].'%'];
            }
        }

        $where[] = ['a.learning_id', '=', $get['learning_id']];
        $where[] = ['a.status', '=', 1];


        if (isset($get['field']) && isset($get['order'])) {
            if (!empty($get['field']) && !empty($get['order'])) {
                $field = $get['field'];
                $sort = $get['order'];
            } else {
                $field = 'id';
                $sort = 'asc';
            }
        } else {
            $field = 'id';
            $sort = 'asc';
        }


        //+学习人数
        $subQuery1 = Db::name('learning_log')
            ->where('learning_id', '=', $get['learning_id'])
            ->field('COUNT(userid) as total, resource_id')
            ->group('resource_id')
            ->buildSql();

        //+学习时长
        $subQuery2 = Db::name('learning_log')
            ->where('learning_id', '=', $get['learning_id'])
            ->field('SUM(learning_time) as duration, resource_id')
            ->group('resource_id')
            ->buildSql();

        //+播放量
        $subQuery3 = Db::name('learning_play_record')
            ->where('learning_id', '=', $get['learning_id'])
            ->field('COUNT(id) as play_total, resource_id')
            ->group('resource_id')
            ->buildSql();

        //+完成人数
        $subQuery4 = Db::name('learning_log')
            ->where('learning_id', '=', $get['learning_id'])
            ->where('is_finish', '=', 1)
            ->field('COUNT(userid) as complete_total, resource_id')
            ->group('resource_id')
            ->buildSql();


        // 取得满足条件的记录数
        $total = Db::table('o_learning_step_resource')
            ->alias('a')
            ->join('o_courseware f', 'f.id=a.courseware_id', 'left')
            ->join($subQuery1.'b','a.id=b.resource_id','left')
            ->join($subQuery2.'c','a.id=c.resource_id','left')
            ->join($subQuery3.'d','a.id=d.resource_id','left')
            ->join($subQuery4.'e','a.id=e.resource_id','left')
            ->where($where)
            ->where('f.type', '<>', 5)
            ->count();

        // 分页查询数据
        $rows = Db::table('o_learning_step_resource')
            ->alias('a')
            ->join('o_courseware f', 'f.id=a.courseware_id', 'left')
            ->join($subQuery1.'b','a.id=b.resource_id','left')
            ->join($subQuery2.'c','a.id=c.resource_id','left')
            ->join($subQuery3.'d','a.id=d.resource_id','left')
            ->join($subQuery4.'e','a.id=e.resource_id','left')
            ->where($where)
            ->where('f.type', '<>', 5)
            ->order($field, $sort)
            ->page($get['page'], $get['limit'])
            ->field('a.id, a.learning_id, f.title, f.type, f.update_time, b.total, c.duration, d.play_total, e.complete_total')
            ->select()
            ->toArray();

        foreach ($rows as &$v) {
            if (empty($v['total'])) {
                $v['total'] = 0;
            }

            if (empty($v['duration'])) {
                $v['duration'] = 0;
            } else {
                $v['duration'] = get_se_to_time($v['duration']);
            }

            if (empty($v['play_total'])) {
                $v['play_total'] = 0;
            }

            if (empty($v['complete_total'])) {
                $v['complete_total'] = 0;
            }

            if ($v['type'] == 1) {
                $v['type_name'] = '<span style="color: #1E9FFF;">图文</span>';
            } elseif ($v['type'] == 2) {
                $v['type_name'] = '<span style="color: #FFB800;">文档</span>';
            } elseif ($v['type'] == 3) {
                $v['type_name'] = '<span style="color: #FF5722;">测试</span>';
            } elseif ($v['type'] == 4) {
                $v['type_name'] = '<span style="color: #FF5722;">Survey</span>';
            }
        }

        $data = array(
            'code'=> 0,
            'msg'=> '',
            'count' => $total,
            'data'=> $rows
        );
        return json($data);
    }


    public function exportChapterrList(Request $request)
    {
        if(!check_jurisdiction(session('admin_auth.role_id'), $this->control, $this->action)) {
            return $this->outputAjax('无操作权限');
        }

        $get = $request->get();

        $where = [];
        if (isset($get['type'])) {
            if (!empty($get['type'])) {
                $where[] = ['f.type', '=', $get['type']];
            }
        }

        if (isset($get['title'])) {
            if (!empty($get['title'])) {
                $where[] = ['f.title', 'like', '%'.$get['title'].'%'];
            }
        }

        $where[] = ['a.learning_id', '=', $get['learning_id']];


        set_time_limit(0);
        //设置程序运行内存
        ini_set('memory_limit', '4096M');

        $fileName = '员工个人Learning学习数据'.date('Ymd', time());
        header('Content-Encoding: UTF-8');
        header("Content-type:application/vnd.ms-excel;charset=UTF-8");
        header('Content-Disposition: attachment;filename="' . $fileName . '.csv"');


        //打开php标准输出流
        $fp = fopen('php://output', 'a');

        //添加BOM头，以UTF8编码导出CSV文件，如果文件头未添加BOM头，打开会出现乱码。
        fwrite($fp, chr(0xEF).chr(0xBB).chr(0xBF));



        //添加导出标题
        fputcsv($fp, ['章节名称', '类型', '学习人数', '完成人数', '学习时长(H)', '播放量', '更新时间']);



        //+学习人数
        $subQuery1 = Db::name('learning_log')
            ->where('learning_id', '=', $get['learning_id'])
            ->field('COUNT(userid) as total, resource_id')
            ->group('resource_id')
            ->buildSql();

        //+学习时长
        $subQuery2 = Db::name('learning_log')
            ->where('learning_id', '=', $get['learning_id'])
            ->field('SUM(learning_time) as duration, resource_id')
            ->group('resource_id')
            ->buildSql();

        //+播放量
        $subQuery3 = Db::name('learning_play_record')
            ->where('learning_id', '=', $get['learning_id'])
            ->field('COUNT(id) as play_total, resource_id')
            ->group('resource_id')
            ->buildSql();

        //+完成人数
        $subQuery4 = Db::name('learning_log')
            ->where('learning_id', '=', $get['learning_id'])
            ->where('is_finish', '=', 1)
            ->field('COUNT(userid) as complete_total, resource_id')
            ->group('resource_id')
            ->buildSql();


        // 取得满足条件的记录数
        $total = Db::table('o_learning_step_resource')
            ->alias('a')
            ->join($subQuery1.'b','a.id=b.resource_id','left')
            ->join($subQuery2.'c','a.id=c.resource_id','left')
            ->join($subQuery3.'d','a.id=d.resource_id','left')
            ->join($subQuery4.'e','a.id=e.resource_id','left')
            ->join('o_courseware f', 'f.id=a.courseware_id', 'left')
            ->where($where)
            ->where('f.type', '<>', 5)
            ->where('a.status', '=', 1)
            ->count();

        $nums = 1000; //每次导出数量
        $step = ceil($total/$nums); //循环次数

        for($i = 0; $i < $step; $i++) {
            $start = $i * $nums;
            // 分页查询数据
            $rows = Db::table('o_learning_step_resource')
                ->alias('a')
                ->join($subQuery1.'b','a.id=b.resource_id','left')
                ->join($subQuery2.'c','a.id=c.resource_id','left')
                ->join($subQuery3.'d','a.id=d.resource_id','left')
                ->join($subQuery4.'e','a.id=e.resource_id','left')
                ->join('o_courseware f', 'f.id=a.courseware_id', 'left')
                ->where($where)
                ->where('f.type', '<>', 5)
                ->where('a.status', '=', 1)
                ->limit($start, $nums)
                ->field('a.id, a.learning_id, f.title, f.type, f.update_time, b.total, c.duration, d.play_total, e.complete_total')
                ->select()
                ->toArray();
            foreach ($rows as $value) {
                if (empty($value['total'])) {
                    $value['total'] = 0;
                }

                if (empty($value['duration'])) {
                    $value['duration'] = 0;
                } else {
                    $value['duration'] = get_se_to_time($value['duration']);
                }

                if (empty($value['play_total'])) {
                    $value['play_total'] = 0;
                }

                if (empty($value['complete_total'])) {
                    $value['complete_total'] = 0;
                }

                if ($value['type'] == 1) {
                    $value['type_name'] = '图文';
                } elseif ($value['type'] == 2) {
                    $value['type_name'] = '文档';
                } elseif ($value['type'] == 3) {
                    $value['type_name'] = '测试';
                } elseif ($value['type'] == 4) {
                    $value['type_name'] = 'Survey';
                }


                $arr = [
                    $value['title'],
                    $value['type_name'],
                    $value['total'],
                    $value['complete_total'],
                    $value['duration'],
                    $value['play_total'],
                    $value['update_time'] ? date('Y-m-d H:i:s', $value['update_time']) : ''
                ];
                fputcsv($fp, $arr);
            }
            //每1千条数据就刷新缓冲区
            ob_flush();
            flush();
        }
    }



    public function chapterView(Request $request)
    {
        $resource_id = $request->get('resource_id');
        View::assign('resource_id', $resource_id);
        return View::fetch();
    }

    //+数据加载
    public function getChapterViewList(Request $request)
    {

        if(!check_jurisdiction(session('admin_auth.role_id'), $this->control, $this->action)) {
            return json(['code' => 500, 'data' => '', 'msg' => '无操作权限']);
        }
        $get = $request->get();

        $where = [];
        if (isset($get['tree_select_nodeId'])) {
            if (!empty($get['tree_select_nodeId'])) {
                $department = $get['tree_select_nodeId'];
                $arr = get_children($department, 1);
                $arr[] = $department;
                $department = implode('|', $arr);
                $where[]  = ['', 'EXP', Db::raw("CONCAT(',',b.department,',') REGEXP concat(',(',replace('".$department."',',','|'),'),')")];
            }
        }

        if (isset($get['status'])) {
            if ($get['status'] != '') {
                $where[] = ['a.is_finish', '=', $get['status']];
            }
        }

        if (isset($get['time'])) {
            if (!empty($get['time'])) {
                $t = explode(' - ', $get['time']);
                $where[] = ['a.add_time', 'between', [strtotime($t[0]), strtotime($t[1])+86399]];
            }
        }

        if (!empty($get['keys'])) {
            $where[] = ['b.name|b.userid', 'like', '%' . $get['keys'] . '%'];
        }
        $where[] = ['a.resource_id', '=', $get['resource_id']];


        // 取得满足条件的记录数
        $total = Db::table('o_learning_log')
            ->alias('a')
            ->join('o_members b','a.userid=b.userid','left')
            ->where($where)
            ->count();

        $rows = Db::table('o_learning_log')
            ->alias('a')
            ->join('o_members b','a.userid=b.userid','left')
            ->where($where)
            ->field('a.id, a.learning_id, a.resource_id, a.add_time, a.learning_time, a.is_finish, a.end_time, b.userid, b.name, b.department_name, b.division')
            ->page($get['page'], $get['limit'])
            ->select()->toArray();

        foreach ($rows as &$v) {
            $v['duration'] = get_se_to_time($v['learning_time']);

            if ($v['is_finish'] == 0) {
                $v['end_time'] = '--------';
            } else {
                $v['end_time'] = date('Y-m-d H:i', $v['end_time']);
            }

            $count = mb_strlen($v['userid']);
            if ($count == 6) {
                if (is_numeric($v['userid'])) {
                    $v['userid'] = '00'.$v['userid'];
                }
            }
        }

        $data = array(
            'code'=> 0,
            'msg'=> '',
            'count' => $total,
            'data'=> $rows
        );
        return json($data);
    }



    public function exportChapterViewList(Request $request)
    {
        if(!check_jurisdiction(session('admin_auth.role_id'), $this->control, $this->action)) {
            return $this->outputAjax('无操作权限');
        }

        $get = $request->get();

        $where = [];
        if (isset($get['tree_select_nodeId'])) {
            if (!empty($get['tree_select_nodeId'])) {
                $department = $get['tree_select_nodeId'];
                $arr = get_children($department, 1);
                $arr[] = $department;
                $department = implode('|', $arr);
                $where[]  = ['', 'EXP', Db::raw("CONCAT(',',b.department,',') REGEXP concat(',(',replace('".$department."',',','|'),'),')")];
            }
        }

        if (isset($get['status'])) {
            if ($get['status'] != '') {
                $where[] = ['a.is_finish', '=', $get['status']];
            }
        }

        if (isset($get['time'])) {
            if (!empty($get['time'])) {
                $t = explode(' - ', $get['time']);
                $where[] = ['a.add_time', 'between', [strtotime($t[0]), strtotime($t[1])+86399]];
            }
        }

        if (!empty($get['keys'])) {
            $where[] = ['b.name|b.userid', 'like', '%' . $get['keys'] . '%'];
        }
        $where[] = ['a.resource_id', '=', $get['resource_id']];


        set_time_limit(0);
        //设置程序运行内存
        ini_set('memory_limit', '4096M');

        $fileName = '员工个人Learning章节学习详情数据'.date('Ymd', time());
        header('Content-Encoding: UTF-8');
        header("Content-type:application/vnd.ms-excel;charset=UTF-8");
        header('Content-Disposition: attachment;filename="' . $fileName . '.csv"');


        //打开php标准输出流
        $fp = fopen('php://output', 'a');

        //添加BOM头，以UTF8编码导出CSV文件，如果文件头未添加BOM头，打开会出现乱码。
        fwrite($fp, chr(0xEF).chr(0xBB).chr(0xBF));



        //添加导出标题
        fputcsv($fp, ['姓名', '工号', 'Division', '部门', '学习时长(H)', '学习状态', '开始学习数据', '完成时间']);

        // 取得满足条件的记录数
        $total = Db::table('o_learning_log')
            ->alias('a')
            ->join('o_members b','a.userid=b.userid','left')
            ->where($where)
            ->count();


        $nums = 1000; //每次导出数量
        $step = ceil($total/$nums); //循环次数

        for($i = 0; $i < $step; $i++) {
            $start = $i * $nums;
            // 分页查询数据
            $rows = Db::table('o_learning_log')
                ->alias('a')
                ->join('o_members b','a.userid=b.userid','left')
                ->where($where)
                ->field('a.id, a.learning_id, a.resource_id, a.add_time, a.learning_time, a.is_finish, a.end_time, b.userid, b.name, b.department_name, b.division')
                ->limit($start, $nums)
                ->select()
                ->toArray();
            foreach ($rows as $value) {
                $duration = get_se_to_time($value['learning_time']);

                if ($value['is_finish'] == 0) {
                    $is_finish = '学习中';
                } else {
                    $is_finish = '已完成';
                }

                if ($value['is_finish'] == 0) {
                    $end_time = '--------';
                } else {
                    $end_time = date('Y-m-d H:i', $value['end_time']);
                }

                $count = mb_strlen($value['userid']);
                if ($count == 6) {
                    if (is_numeric($value['userid'])) {
                        $carolid = "'00" . $value['userid'];
                    } else {
                        $carolid = $value['userid'];
                    }

                } else {
                    $carolid = $value['userid'];
                }
                $arr = [
                    $value['name'],
                    $carolid,
                    $value['division'],
                    $value['department_name'],
                    $duration,
                    $is_finish,
                    date('Y-m-d H:i:s', $value['add_time']),
                    $end_time
                ];
                fputcsv($fp, $arr);
            }
            //每1千条数据就刷新缓冲区
            ob_flush();
            flush();
        }
    }
}